<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Chapter&nbsp;15.&nbsp;Deployment Guide</title>
<link href="../docbook.css" type="text/css" rel="stylesheet">
<meta content="DocBook XSL-NS Stylesheets V1.76.1" name="generator">
<meta name="keywords" content="Hsqldb, HyperSQL, SQL">
<meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java">
<link rel="home" href="index.html" title="HyperSQL User Guide">
<link rel="up" href="index.html" title="HyperSQL User Guide">
<link rel="prev" href="unix-chapt.html" title="Chapter&nbsp;14.&nbsp;HyperSQL on UNIX">
<link rel="next" href="lists-app.html" title="Appendix&nbsp;A.&nbsp;Lists of Keywords">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table summary="Navigation header" width="100%">
<tr>
<td align="left" width="30%"><a accesskey="p" href="unix-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a>&nbsp;</td><td align="center" width="40%" style="font-weight:bold;">Chapter&nbsp;15.&nbsp;Deployment Guide</td><td align="right" width="30%">&nbsp;<a accesskey="n" href="lists-app.html"><img src="../images/db/next.png" alt="Next"></a></td>
</tr>
<tr>
<td valign="top" align="left" width="30%">Chapter&nbsp;14.&nbsp;HyperSQL on UNIX&nbsp;</td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%">&nbsp;Appendix&nbsp;A.&nbsp;Lists of Keywords</td>
</tr>
</table>
</div>
<HR>
<div class="chapter" title="Chapter&nbsp;15.&nbsp;Deployment Guide">
<div class="titlepage">
<div>
<div>
<h2 class="title">
<a name="deployment-chapt"></a>Chapter&nbsp;15.&nbsp;Deployment Guide</h2>
</div>
<div>
<div class="authorgroup">
<div class="author">
<h3 class="author">
<span class="firstname">Fred</span> <span class="surname">Toussi</span>
</h3>
<div class="affiliation">
<span class="orgname">The HSQL Development Group<br>
</span>
</div>
</div>
</div>
</div>
<div>
<p class="releaseinfo">$Revision: 5256 $</p>
</div>
<div>
<div class="legalnotice" title="Legal Notice">
<a name="N163B2"></a>
<p>Copyright 2002-2013 Fred Toussi. Permission is granted to
      distribute this document without any alteration under the terms of the
      HSQLDB license. Additional permission is granted to the HSQL Development
      Group to distribute this document with or without alterations under the
      terms of the HSQLDB license.</p>
</div>
</div>
<div>
<p class="pubdate">2014-02-13 18:22:10-0500</p>
</div>
</div>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_mem_disk_use">Memory and Disk Use</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_table_mem_use">Table Memory Allocation</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_result_mem_use">Result Set Memory Allocation</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_temp_mem_use">Temporary Memory Use During Operations</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_cache_mem_use">Data Cache Memory Allocation</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_pool_mem_use">Object Pool Memory Allocation</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_lob_mem_use">Lob Memory Usage</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_disk_space">Disk Space</a></span>
</dt>
</dl>
</dd>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_managing_connections">Managing Database Connections</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_app_dev_testing">Application Development and Testing</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_tweaking">Tweaking the Mode of Operation</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_embedded_desktop_db">Embedded Databases in Desktop Applications</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_embedded_server_db">Embedded Databases in Server Applications</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_mixed_mode_server">Mixed Mode : Embedding a HyperSQL Server (Listener)</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_no_logging">Using HyperSQL Without Logging Data Change</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_bulk_operations">Bulk Inserts, Updates and Deletes</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_nio_access">Using NIO File Access</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_server_db">Server Databases</a></span>
</dt>
</dl>
</dd>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_upgrade_database">Upgrading Databases</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_upgrade_via_script">Upgrading From Older
      Versions</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_script_manual_change">Manual Changes to the *.script File</a></span>
</dt>
</dl>
</dd>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_backware_compatibility">Backward Compatibility Issues</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_dependency_applications">HyperSQL Dependency Settings for Applications</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_hsqldb_version_pull">What version to Pull</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_snapshot_repos">Using the HyperSQL Snapshot Repository</a></span>
</dt>
<dt>
<span class="section"><a href="deployment-chapt.html#dec_maven_range_version">Range Versioning</a></span>
</dt>
</dl>
</dd>
</dl>
</div>
<div class="section" title="Memory and Disk Use">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_mem_disk_use"></a>Memory and Disk Use</h2>
</div>
</div>
</div>
<a name="N163B9" class="indexterm"></a>
<p>Memory used by the program can be thought of as two distinct pools:
    memory used for table data which is not released unless the data is
    deleted and memory that can be released or is released automatically,
    including memory used for caching, building result sets and other internal
    operations such as storing the information needed for a rollback a
    transaction.</p>
<p>Most JVM implementations allocate up to a maximum amount of memory
    (usually 64 MB by default). This amount is generally not adequate when
    large memory tables are used, or when the average size of rows in cached
    tables is larger than a few hundred bytes. The maximum amount of allocated
    memory can be set on the Java command line that is used for running
    HyperSQL. For example, with Sun JVM, parameter -Xmx256m increases the
    amount to 256 MB.</p>
<div class="section" title="Table Memory Allocation">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_table_mem_use"></a>Table Memory Allocation</h3>
</div>
</div>
</div>
<p>The memory used for a MEMORY table is the sum of memory used by
      each row. Each MEMORY table row is a Java object that has 2 int or
      reference variables. It contains an array of objects for the fields in
      the row. Each field is an object such as <code class="classname">Integer</code>,
      <code class="classname">Long</code>, <code class="classname">String</code>, etc. In
      addition each index on the table adds a node object to the row. Each
      node object has 6 int or reference variables. As a result, a table with
      just one column of type INTEGER will have four objects per row, with a
      total of 10 variables of 4 bytes each - currently taking up 80 bytes per
      row. Beyond this, each extra column in the table adds at least a few
      bytes to the size of each row.</p>
</div>
<div class="section" title="Result Set Memory Allocation">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_result_mem_use"></a>Result Set Memory Allocation</h3>
</div>
</div>
</div>
<p>By default, all the rows in the result set are built in memory, so
      very large result sets may not be possible to build. A server mode
      databases releases the result set from the server memory once the
      database server has returned the result set. An
      <em class="glossterm">in-process</em> database releases the memory when the
      application program closes the <code class="classname">java.sql.ResultSet</code>
      object. A server mode database requires additional memory for returning
      result sets, as it converts the full result set into an array of bytes
      which is then transmitted to the client.</p>
<p>HyperSQL 2.0 supports disk-based result sets. The commands,
      <code class="literal">SET SESSION RESULT MEMORY ROWS &lt;integer&gt;</code> and
      <code class="literal">SET DATABASE DEFAULT RESULT MEMORY ROWS
      &lt;integer&gt;</code> specify a threshold for the number of rows.
      Results with row counts above the threshold are stored on disk. These
      settings also apply to temporary tables, views and subquery
      tables.</p>
<p>Disk-based result sets slow down the database operations and
      should be used only when absolutely necessary, perhaps with result sets
      that are larger than tens of thousands of rows.</p>
<p>In a server mode database, when the setFetchSize() method of the
      Statement interface is used to limit the number rows fetched, the whole
      result is held by the engine and is returned to the JDBC ResultSet in
      blocks of rows of the specified fetch size.</p>
</div>
<div class="section" title="Temporary Memory Use During Operations">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_temp_mem_use"></a>Temporary Memory Use During Operations</h3>
</div>
</div>
</div>
<p>When UPDATE and DELETE queries are performed on CACHED tables, the
      full set of rows that are affected, including those affected due to ON
      UPDATE actions, is held in memory for the duration of the operation.
      This means it may not be possible to perform deletes or updates
      involving very large numbers of rows of CACHED tables. Such operations
      should be performed in smaller sets. This memory is released as soon as
      the DELETE or UPDATE is performed.</p>
<p>When transactions support is enabled with SET AUTOCOMMIT FALSE,
      lists of all insert, delete or update operations are stored in memory so
      that they can be undone when ROLLBACK is issued. For CACHED tables, only
      the transaction information is held in memory, not the actual rows that
      have changed. Transactions that span thousands of modification to data
      will take up a lot of memory until the next COMMIT or ROLLBACK clears
      the list. Each row modification uses less than 100 bytes until
      COMMIT.</p>
<p>When subqueries or views are used in SELECT and other statements,
      transient tables are created and populated by the engine. If the
      <code class="literal">SET SESSION RESULT MEMORY ROWS &lt;integer&gt;</code>
      statement has been used, these transient tables are stored on disk when
      they are larger than the threshold.</p>
</div>
<div class="section" title="Data Cache Memory Allocation">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_cache_mem_use"></a>Data Cache Memory Allocation</h3>
</div>
</div>
</div>
<p>With CACHED tables, the data is stored on disk and only up to a
      maximum number of rows are held in memory at any time. The default is up
      to 50,000 rows. The SET FILES CACHE ROWS command or the
      <span class="property">hsqldb.cache_rows</span> connection property can be set to
      alter this amount. As any random subset of the rows in any of the CACHED
      tables can be held in the cache, the amount of memory needed by cached
      rows can reach the sum of the rows containing the largest field data.
      For example if a table with 100,000 rows contains 40,000 rows with 1,000
      bytes of data in each row and 60,000 rows with 100 bytes in each, the
      cache can grow to contain 50,000 of the smaller rows, but as explained
      further, only 10,000 or the large rows.</p>
<p>An additional property, <span class="property">hsqldb.cache_size</span> is
      used in conjunction with the <span class="property">hsqldb.cache_rows</span>
      property. This puts a limit in bytes on the total size of rows that are
      cached. The default values is 10,000KB. (This is the size of binary
      images of the rows and indexes. It translates to more actual memory,
      typically 2-4 times, used for the cache because the data is represented
      by Java objects.)</p>
<p>If memory is limited, the <span class="property">hsqldb.cache_rows</span>
      or <span class="property">hsqldb.cache_size</span> database properties can be
      reduced. In the example above, if the
      <span class="property">hsqldb.cache_size</span> is reduced from 10,000 to 5,000,
      it will allow the number of cached rows to reach 50,000 small rows, but
      only 5,000 of the larger rows.</p>
<p>Data for CLOB and BLOB columns is not cached and does not affect
      the CACHED table memory cache.</p>
<p>The use of Java NIO file access method also increases memory
      usage. Access with NIO improves database update speed and is used by
      default for data files up to 256 MB. For minimal memory use, nio access
      should be disabled.</p>
<p>The operating system usually allocates a large amount of buffer
      memory for speed up file read operations. Therefore when a lot of memory
      is available to the operating system, all database operations perform
      faster.</p>
</div>
<div class="section" title="Object Pool Memory Allocation">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_pool_mem_use"></a>Object Pool Memory Allocation</h3>
</div>
</div>
</div>
<p>HyperSQL uses a set of fast pools for immutable objects such as
      Integer, Long and short String objects that are stored in the database.
      In most circumstances, this reduces the memory footprint still further
      as fewer copies of the most frequently-used objects are kept in memory.
      The object pools are shared among all databases in the JVM. The size of
      each pool can be modified only by altering and recompiling the
      <code class="literal">org.hsqldb.store.ValuePool</code> class.</p>
</div>
<div class="section" title="Lob Memory Usage">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_lob_mem_use"></a>Lob Memory Usage</h3>
</div>
</div>
</div>
<p>Access to lobs is always performed in chunks, so it is perfectly
      possible to store and access a CLOB or BLOB that is larger than the JVM
      memory allocation. The actual total size of lobs is almost unlimited. We
      have tested with over 100 GB of lobs without any loss of
      performance.</p>
<p>By default, HyperSQL 2 uses memory-based tables for the lob schema
      (not the actual lob data). Therefore it is practical to store about
      100,000 individual lobs in the database with the default JVM memory
      allocation. More lobs can be stored with larger JVM memory allocations.
      In order to store more than a few hundreds of thousands of lobs, you can
      change the lob schema storage to CACHED tables with the following
      statements:</p>
<div class="example">
<a name="N16428"></a>
<p class="title">
<b>Example&nbsp;15.1.&nbsp;Using CACHED tables for the LOB schema</b>
</p>
<div class="example-contents">
<pre class="screen"> SET TABLE SYSTEM_LOBS.BLOCKS TYPE CACHED
 SET TABLE SYSTEM_LOBS.LOBS TYPE CACHED
 SET TABLE SYSTEM_LOBS.LOB_IDS TYPE CACHED
</pre>
</div>
</div>
<br class="example-break">
</div>
<div class="section" title="Disk Space">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_disk_space"></a>Disk Space</h3>
</div>
</div>
</div>
<p>With file: database, the engine uses the disk for storage of data
      and any change. For safely, the engine backs up the data internally
      during operation. Spare space, at least equal to the size of the .data
      and .script file is needed. The .lobs file is not backed up during
      operation as it is not necessary for safety.</p>
</div>
</div>
<div class="section" title="Managing Database Connections">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_managing_connections"></a>Managing Database Connections</h2>
</div>
</div>
</div>
<p>In all running modes (server or <em class="glossterm">in-process</em>)
    multiple connections to the database engine are supported.
    <em class="glossterm">in-process</em> (standalone) mode supports connections
    from the client in the same Java Virtual Machine, while server modes
    support connections over the network from several different
    clients.</p>
<p>Connection pooling software can be used to connect to the database
    but it is not generally necessary. Connection pools may be used for the
    following reasons.</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>To allow new queries to be performed while a time-consuming
        query is being performed in the background. In HyperSQL, blocking
        depends on the transaction control model, the isolation level, and the
        current activity by other sessions.</p>
</li>
<li class="listitem">
<p>To limit the maximum number of simultaneous connections to the
        database for performance reasons. With HSQLDB this can be useful if
        your application is designed in a way that opens and closes
        connections for each small task. Also, the overall performance may be
        higher when fewer simultaneous connections are used. If you want to
        reduce the number of simultaneous sessions, you can use a connection
        pool with fewer pooled connections.</p>
</li>
</ul>
</div>
<p>An application that is not both multi-threaded and transactional,
    such as an application for recording user login and logout actions, does
    not need more than one connection. The connection can stay open
    indefinitely and reopened only when it is dropped due to network
    problems.</p>
<p>When using an <em class="glossterm">in-process</em> database, when the
    last connection to the database is closed, the database still remains
    open, waiting for the next connection to be made. From version 2.2.9, each
    time the last connection is closed all the data changes are logged and
    synched to disk.</p>
<p>An explicit SHUTDOWN command, with or without an argument, is
    required to close the database. A connection property, shutdown=true, can
    be used on the connection URL or in a properties object to shutdown the
    database when the last connection is closed.</p>
<p>When using a server database (and to some extent, an
    <em class="glossterm">in-process</em> database), care must be taken to avoid
    creating and dropping JDBC Connections too frequently. Failure to observe
    this will result in poor performance when the application is under heavy
    load.</p>
<p>A common error made by users in load-test simulations is to use a
    single client machine to open and close thousands of connections to a
    HyperSQL server instance. The connection attempts will fail after a few
    thousand because of OS restrictions on opening sockets and the delay that
    is built into the OS in closing them.</p>
</div>
<div class="section" title="Application Development and Testing">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_app_dev_testing"></a>Application Development and Testing</h2>
</div>
</div>
</div>
<p>First thing to be aware of is the SQL conformance settings of
    HyperSQL. By default version 2 applies stricter conformance rules than
    version 1.8 and catches long strings or decimal values that do not fit
    within the specified length or precision settings. However, there are
    several conformance settings that are turned off by default. This is to
    enable easier migration from earlier versions, and also greater
    compatibility with databases such as MySQL that are sometimes very liberal
    with type conversions. The conformance settings are listed in the System
    Management chapter and their connection property equivalents are listed in
    the Database Properties chapter. Ideally, all the settings that are not
    for syntax compatibility with other databases should have a true value for
    best error checking. You can turn on the settings for syntax compatibility
    with another database if you are porting or testing applications targeted
    at the other database.</p>
<p>For application unit testing you can use an all-in-memory,
    in-process database.</p>
<p>If the tests are all run in one process, then the contents of a
    <em class="glossterm">mem:</em> database survives between tests. To release
    the contents you can use the SHUTDOWN command (an SQL command). You can
    even use multiple <em class="glossterm">mem:</em> databases in your tests and
    SHUTDOWN each one separately.</p>
<p>If the tests are in different processes and you want to keep the
    data between the tests, the best solution is to use a Server instance that
    has a <em class="glossterm">mem:</em> database. After the tests are done, you
    can SHUTDOWN this database, which will shutdown the server.</p>
<p>The Server has an option that allows databases to be created as
    needed by making a connection (see the Listeners Chapter). This option is
    useful for testing, as your server is never shut down when a database is
    shutdown. Each time you connect to the <em class="glossterm">mem:</em>
    database that is served by the Server, the database is created if it does
    not exist (i.e. has been previously shut down).</p>
<p>If you do not want to run a Server instance, and you need
    persistence between tests in different processes, then you should use a
    <em class="glossterm">file:</em> database. From version 2.2.9 when the last
    existing connection to the database is closed, the latest changes to the
    database are persisted fully with fsync. The database is still in an open
    state until it is shut down. You can use the
    <code class="literal">shutdown=true</code> connection property to close the database
    automatically after the connections are closed. The automatic sync and
    shutdown are mainly for test environment. In production environments you
    should execute the SHUTDOWN statement before your application is closed.
    This ensurs a quick start next time you connect to the database.</p>
<p>An alternative option is to use
    <code class="literal">hsqldb.write_delay=false</code> connection property, but this
    is slightly slower than the other option and should be used in situations
    where the test application does not close the connections. This option
    uses fsync after each commit. Even if the test process is aborted without
    shutting down the connections, all committed data is saved. It has been
    reported that some data access frameworks do not close all their
    connection to the database after the tests. In such situations, you need
    to use this option if you want the data to persist at the end of the
    tests</p>
<p>You may actually want to use a <em class="glossterm">file:</em>
    database, or a server instance that serves a <em class="glossterm">file:</em>
    database in preference to a <em class="glossterm">mem:</em> database. As
    HyperSQL logs the DDL and DML statements in the .log file, this file can
    be used to check what is being sent to the database. Note that UPDATE
    statements are represented by a DELETE followed by an INSERT statement.
    Statements are written out when the connection commits. The write delay
    also has an effect on how soon the statements are written out. By default,
    the write delay is 0.5 second.</p>
<p>The new SQL logging feature in version 2.2.x and later records all
    executed statements and can be used for debugging your application.</p>
<p>Some types of tests start with a database that already contains the
    tables and data, and perform various operations on it during the tests.
    You can create and populate the initial database then set the property
    "files_readonly=true" in the .properties file of the database. The tests
    can then modify the database, but these modifications are not persisted
    after the tests have completed.</p>
<p>Databases with "files_readonly=true" can be placed within the
    classpath and in a jar file. In this case, the connection URL must use the
    res: protocol, which treats the database as a resource.</p>
</div>
<div class="section" title="Tweaking the Mode of Operation">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_tweaking"></a>Tweaking the Mode of Operation</h2>
</div>
</div>
</div>
<p>Different modes of operation and settings are used for different
    purposes. Some scenarios are discussed below:</p>
<div class="section" title="Embedded Databases in Desktop Applications">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_embedded_desktop_db"></a>Embedded Databases in Desktop Applications</h3>
</div>
</div>
</div>
<p>In this usage, the amount of data change is often limited and
      there is often a requirement to persist the data immediately. The
      default write delay of 0.5 second is fine for many applications. You can
      also use the property <code class="literal">hsqldb.write_delay_millis=100</code>
      to reduce it to 0.1 second, or the property
      <code class="literal">hsqldb.write_delay=false</code> to force a disk fsync after
      each commit. Before the application is closed, you should perform the
      SHUTDOWN command to ensure the database is opened instantly when it is
      next opened. Note you don't need to use SHUTDOWN COMPACT as
      routine.</p>
</div>
<div class="section" title="Embedded Databases in Server Applications">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_embedded_server_db"></a>Embedded Databases in Server Applications</h3>
</div>
</div>
</div>
<p>This usage involves a server application, such as a web
      application, connecting to an embedded HyperSQL instance. In this usage,
      the database is often accessed heavily, therefore performance and
      latency is a consideration. If the database is updated heavily, the
      default value of the WRITE DELAY property (0.5 sec) is often enough, as
      it is assumed the server or the application does not go down frequently.
      If it is necessary, you can reduce the WRITE DELAY to a small value (20
      ms) without impacting the update speed. If you reduce WRITE DELAY to
      zero, performance drops to the speed of disk file sync operation.</p>
<p>Alternatively, a server application can use an all-in-mem database
      instance for fast access, while sending the data changes to a
      persistent, disk based instance either periodically or in real
      time.</p>
</div>
<div class="section" title="Mixed Mode : Embedding a HyperSQL Server (Listener)">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_mixed_mode_server"></a>Mixed Mode : Embedding a HyperSQL Server (Listener)</h3>
</div>
</div>
</div>
<p>Since you won't be able to access
      <em class="glossterm">in-process</em> database instances from other
      processes, you will often want to run a Listener in your applications
      that use embedded databases. You can do this by starting up a Server or
      WebServer instance programmatically, but you could also use the class
      <code class="classname">org.hsqldb.util.MainInvoker</code> to start up your
      application and a HyperSQL Server or WebServer without any programming.
      MainInvoker is a general-purpose utility class to invoke the main
      methods of multiple classes. Each main class is followed by its
      arguments (if any), then an empty string to separate it from the next
      main class.</p>
<div class="example">
<a name="N164B6"></a>
<p class="title">
<b>Example&nbsp;15.2.&nbsp;MainInvoker Example</b>
</p>
<div class="example-contents">
<pre class="screen">  java -cp path/to/your/app.jar:path/to/hsqldb.jar org.hsqldb.util.MainInvoker com.your.main.App "" org.hsqldb.server.Server</pre>
</div>
</div>
<p>
<br class="example-break"> (Use ; instead of : to delimit classpath elements on
      Windows). The empty string separates your com.your.main.App invocation
      from the org.hsqldb.server.</p>
<p>Specify the same <em class="glossterm">in-process</em> JDBC URL to
      your app and in the <code class="filename">server.properties</code> file. You can
      then connect to the database from outside using a JDBC URL like
      <code class="literal">jdbc:hsqldb:hsql://hostname</code>, while connecting from
      inside the application using something like
      <code class="literal">jdbc:hsqldb:file:&lt;filepath of database&gt;</code>
      .</p>
<p>This tactic can be used to run off-the-shelf server
      applications with an embedded HyperSQL Server, without doing any
      coding.</p>
<p>
<code class="classname">MainInvoker</code> can be used to run any
      number of Java class main method invocations in a single JVM. See the
      API spec for <code class="classname"><a class="classname" href="filelinks-app.html#MainInvoker.html-link">
      MainInvoker</a></code> for details on its usage.</p>
</div>
<div class="section" title="Using HyperSQL Without Logging Data Change">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_no_logging"></a>Using HyperSQL Without Logging Data Change</h3>
</div>
</div>
</div>
<p>All file database that are not readonly, write changes to the .log
      file. There are scenarios where writing to the .log file can be turned
      off to improve performance, especially with larger databases. For these
      applications you can set the property
      <code class="literal">hsqldb.log_data=false</code> to disable the recovery log and
      speed up data change performance. The equivalent SQL command is SET
      FILES LOG FALSE.</p>
<p>With this setting, no data is logged, but all the changes to
      cached tables are written to the .data file. To persist all the data
      changes up to date, you can use the CHECKPOINT command. If you perform
      SHUTDOWN, the data is also persisted correctly. If you do not use
      CHECKPOINT or SHUTDOWN when you terminate the application, all the
      changes are lost and the database reverts to its original state when it
      is opened without losing any of the original data.</p>
<p>Your server applications can use a database as a temporary disk
      data cache which is not persisted past the lifetime of the application.
      For this usage, delete the database files when the application
      ends.</p>
<p>On some platforms, such as embedded devices which have a reliable
      storage device, this is also a useful option. Your application issues
      CHECKPOINT to save the changes made so far. This method of use reduces
      write operations on SSD devices. For this usage, the lock file should
      also be disabled with the connection property
      <code class="literal">hsqldb.lock_file=false</code>.</p>
</div>
<div class="section" title="Bulk Inserts, Updates and Deletes">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_bulk_operations"></a>Bulk Inserts, Updates and Deletes</h3>
</div>
</div>
</div>
<p>Bulk inserts, deletes and updates are performed with the best
      performance with the following method. The database remains safe and
      consistent using this method. In the event of a machine crash during the
      operation, the database can be recovered to the point just before the
      bulk operation.</p>
<div class="orderedlist">
<ol class="orderedlist" type="1">
<li class="listitem">
<p>Before the operation, execute the SET FILES LOG FALSE
          statement.</p>
</li>
<li class="listitem">
<p>Execute the CHECKPOINT statement.</p>
</li>
<li class="listitem">
<p>Perform all the bulk operations, using batched prepared
          statements. A batch size of 1000 to 10000 is adequate. Perform
          commit after each batch.</p>
</li>
<li class="listitem">
<p>After all the bulk operations are complete, execute the SET
          FILES LOG TRUE statement.</p>
</li>
<li class="listitem">
<p>Finally execute the CHECKPOINT statement.</p>
</li>
<li class="listitem">
<p>If you have performed many thousands of updates or deletes
          (not just inserts), it is a good idea to execute CHECKPOINT DEFRAG,
          instead of CHECKPOINT at the end.</p>
</li>
<li class="listitem">
<p>If things go wrong during the bulk operation, for example when
          a unique constraint violation aborts the operation, and you want to
          redo the whole operation, just use SHUTDOWN IMMEDIATELY instead of
          CHECKPOINT. When you restart the database it will revert to the
          state at the first CHECKPOINT and the bulk operation can be
          redone.</p>
</li>
</ol>
</div>
</div>
<div class="section" title="Using NIO File Access">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_nio_access"></a>Using NIO File Access</h3>
</div>
</div>
</div>
<p>This method of file access uses the operating system's
      memory-mapped file buffer for the <code class="literal">.data</code> file. For
      larger databases with CACHED tables, use of nio improves database access
      speed significantly. Performance improvements can be tenfold or even
      higher. By default, NIO is used for .data files from 16 MB up to 256 MB.
      You can increase the limit with the <code class="literal">SET FILES NIO SIZE
      &lt;value&gt;</code> statement. There should be enough RAM available
      to accommodate the memory mapped buffers. For vary large nio usage, a 64
      bit JVM must be used. The memory is not taken from the JVM memory
      allocation, therefore there is no need to increase the -Xmx parameter of
      the JVM. If not enough memory is available for the specified value, nio
      is not used.</p>
</div>
<div class="section" title="Server Databases">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_server_db"></a>Server Databases</h3>
</div>
</div>
</div>
<p>Running databases in a HyperSQL server is the best overall method
      of access. As the JVM process is separate from the application, this
      method is the most reliable as well as the most accessible method of
      running databases.</p>
</div>
</div>
<div class="section" title="Upgrading Databases">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_upgrade_database"></a>Upgrading Databases</h2>
</div>
</div>
</div>
<a name="N16518" class="indexterm"></a>
<p>Any database that is not produced with the release version of
    HyperSQL 2.0 must be upgraded to this version.</p>
<div class="procedure" title="Procedure&nbsp;15.1.&nbsp;Upgrading Databases Created with Version 1.8.x">
<a name="N1651E"></a>
<p class="title">
<b>Procedure&nbsp;15.1.&nbsp;Upgrading Databases Created with Version 1.8.x</b>
</p>
<ol class="procedure" type="1">
<li class="step" title="Step 1">
<p>Open the database with the jar that created it and perform the
        SHUTDOWN statement as an SQL statement.</p>
</li>
<li class="step" title="Step 2">
<p>Open the database with the HyperSQL 2.0 jar.</p>
</li>
<li class="step" title="Step 3">
<p>Perform the SHUTDOWN COMPACT statement..</p>
</li>
</ol>
</div>
<p>The first step is to guarantee there is no .log file for the
    database. When upgrading an application that has been deployed on a large
    scale, it is sometimes not practical to perform the first step of this
    procedure (with the old jar). You can ignore the first step but you may
    lose part of the database statements that are stored in the .log file.
    Therefore you need to test with databases created with your application to
    make sure typical statements that are logged in the .log file are
    compatible with the new version. Examples of known incompatible statements
    are some DDL statements used for changing the data type or default values
    of column.</p>
<p>A note about SHUTDOWN modes. SHUTDOWN COMPACT is equivalent to
    SHUTDOWN SCRIPT plus opening the database and then performing a simple
    SHUTDOWN.</p>
<p>After upgrading a database, you may want to change some of its
    settings. For example, the new SET FILES BACKUP INCREMENT TRUE statement
    can improve the shutdown and checkpoint times of larger databases.</p>
<p>Once a database is upgraded to 2.0, it can no longer be used with
    previous versions of HyperSQL.</p>
<div class="section" title="Upgrading From Older Versions">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_upgrade_via_script"></a>Upgrading From Older
      Versions</h3>
</div>
</div>
</div>
<p>To upgrade from version 1.8.x with the default TEXT format script
      files, follow the instructions above. If the 1.8.x files have database
      script format set to BINARY or COMPRESSED (ZIPPED) you must issue the
      SET SCRIPTFORMAT TEXT and SHUTDOWN SCRIPT commands with the old version,
      then open with the new version of the engine. In most cases the upgrade
      is successful and complete.</p>
<p>It is strongly recommended to execute SHUTDOWN COMPACT after an
      automatic upgrade from previous versions.</p>
<p>If your database has been created with version 1.7.2 or 1.7.3,
      first upgrade to version 1.8.1 and perform a SHUTDOWN SCRIPT with this
      version. You can then upgrade the database to version 2.0.</p>
<p>To upgrade from older version database files (1.7.1 and older)
      that contain CACHED tables, use the SCRIPT procedure below. In all
      versions of HyperSQL, the <code class="literal">SCRIPT 'filename'</code> command
      (used as an SQL statement) allows you to save a full record of your
      database, including database object definitions and data, to a file of
      your choice. You can export a script file using the old version of the
      database engine and open the script as a database with 2.0.</p>
<div class="procedure" title="Procedure&nbsp;15.2.&nbsp;Upgrade Using the SCRIPT Procedure for Very Old Versions">
<a name="N16542"></a>
<p class="title">
<b>Procedure&nbsp;15.2.&nbsp;Upgrade Using the SCRIPT Procedure for Very Old
        Versions</b>
</p>
<ol class="procedure" type="1">
<li class="step" title="Step 1">
<p>Open the original database in the old version of
          DatabaseManager</p>
</li>
<li class="step" title="Step 2">
<p>Issue the SCRIPT command, for example <code class="literal">SCRIPT
          'newversion.script'</code> to create a script file containing a
          copy of the database.</p>
</li>
<li class="step" title="Step 3">
<p>SHUTDOWN this database.</p>
</li>
<li class="step" title="Step 4">
<p>Copy the original <code class="literal">*.properties</code> file into
          <code class="filename">newversion.properties</code> in the same directory as
          <code class="filename">newversion.script</code>
</p>
</li>
<li class="step" title="Step 5">
<p>Try to open the new database <code class="filename">newversion</code>
          using DatabaseManager of version 1.8.1.</p>
</li>
<li class="step" title="Step 6">
<p>If there is any inconsistency in the data, the script line
          number is reported on the console and the opening process is
          aborted. Edit and correct any problems in the
          <code class="filename">newversion.script</code> before attempting to open
          again. Use the guidelines in the next section (Manual Changes to the
          <code class="literal">.script</code> File). Use a programming editor that is
          capable of handling very large files and does not wrap long lines of
          text.</p>
</li>
</ol>
</div>
</div>
<div class="section" title="Manual Changes to the *.script File">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_script_manual_change"></a>Manual Changes to the *.script File</h3>
</div>
</div>
</div>
<p>In HyperSQL 2.0 the full range of ALTER TABLE commands is
      available to change the data structures and their names. However, if an
      old database cannot be opened due to data inconsistencies, or it uses
      index or column names that are not compatible with 2.0, manual editing
      of the <code class="literal">*.script</code> file can be performed and can be
      faster.</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>Version 2.0 does not accept duplicate names for indexes that
          were allowed before 1.7.2.</p>
</li>
<li class="listitem">
<p>Version 2.0 does not accept some table or column names that
          are SQL reserved keywords without double quoting.</p>
</li>
<li class="listitem">
<p>Version 2.0 does not accept unquoted table or column names
          which begin with an underscore, unless the connection
          <code class="literal">sql.regular_names</code> is set false.</p>
</li>
<li class="listitem">
<p>Version 2.0 is more strict with check conditions and default
          values.</p>
</li>
</ul>
</div>
<p>Other manual changes are also possible. Note that the
      <code class="literal">*.script</code> file must be the result of a SHUTDOWN SCRIPT
      and must contain the full data for the database. The following changes
      can be applied so long as they do not affect the integrity of existing
      data.</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>Names</p>
<p>Names of tables, columns and indexes can be changed. These
          changes must be consistent regarding foreign key constraint
          references.</p>
</li>
<li class="listitem">
<p>
<code class="literal">CHECK</code>
</p>
<p>A check constraint can always be removed.</p>
</li>
<li class="listitem">
<p>
<code class="literal">NOT NULL</code>
</p>
<p>A not-null constraint can always be removed.</p>
</li>
<li class="listitem">
<p>
<code class="literal">PRIMARY KEY</code>
</p>
<p>A primary key constraint can be removed. It cannot be removed
          if there is a foreign key referencing the column(s).</p>
</li>
<li class="listitem">
<p>
<code class="literal">UNIQUE</code>
</p>
<p>A UNIQUE constraint can be removed if there is no foreign key
          referencing the column(s).</p>
</li>
<li class="listitem">
<p>
<code class="literal">FOREIGN KEY</code>
</p>
<p>A FOREIGN KEY constraint can always be removed.</p>
</li>
<li class="listitem">
<p>
<code class="literal">COLUMN TYPES</code>
</p>
<p>Some changes to column types are possible. For example an
          INTEGER column can be changed to BIGINT.</p>
</li>
</ul>
</div>
<p>After completing the changes and saving the modified
      <code class="literal">.script</code> file, you can open the database as
      normal.</p>
</div>
</div>
<div class="section" title="Backward Compatibility Issues">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_backware_compatibility"></a>Backward Compatibility Issues</h2>
</div>
</div>
</div>
<p>HyperSQL 2 conforms to the SQL Standard better than previous
    versions and has many more features. For these reasons, there may be some
    compatibility issues when converting old database, or using applications
    that were written for version 1.8.x or earlier. Some of the potential
    issues (and enhancements) are listed here. See the full list of connection
    properties for alternatives.</p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">
<p>By default, when comparison strings, the shorter string is
        padded with spaces. This has an effect on comparing
        <code class="literal">'test'</code> and <code class="literal">'test '</code> which are now
        considered equal, despite the length difference. This behaviour is
        controlled by the default PAD SPACE property of collations, which can
        be changed to NO PAD. See the statement <code class="literal">SET DATABASE
        COLLATION &lt;name&gt; [ PAD SPACE | NO PAD ]</code>.</p>
</li>
<li class="listitem">
<p>User names and passwords are case-sensitive. Check the .script
        file of a database for the correct case of user name and password and
        use this form in the connection properties or on connection
        URL.</p>
</li>
<li class="listitem">
<p>It is now possible to specify the admin username and password
        for a new database (instead of SA and the empty password).</p>
</li>
<li class="listitem">
<p>HyperSQL 2.0 has several settings that relax its conformance to
        the SQL Standard in the areas of type conversion and object names.
        These settings can be turned on for maximum conformance.</p>
</li>
<li class="listitem">
<p>Check constraints must conform to the SQL Standard. A check
        constraint is rejected if it is not deterministic or retrospectively
        deterministic. When opening an old database, HyperSQL silently drops
        check constraints that no longer compile. See under check constraints
        for more detail about what is not allowed.</p>
</li>
<li class="listitem">
<p>Type declarations in column definition and in cast expressions
        must have the necessary size parameters.</p>
</li>
<li class="listitem">
<p>In connection with the above, an old database that did not have
        the <code class="literal">enforce_strict_size</code> property, is now converted
        to version 2.0 with the engine supplying the missing size parameters.
        For example, a VARCHAR column declaration that has no size, is given a
        32K size, a LONGVARCHAR column is given a 16MB size. Check these sizes
        are adequate for your use, and change the column definition as
        necessary.</p>
</li>
<li class="listitem">
<p>Column names in a GROUP BY clause were previously resolved to
        the column label. They are now resolved to column name first, and if
        the name does not match, to the column label.</p>
</li>
<li class="listitem">
<p>If two or more tables in a join contain columns with the same
        name, the columns cannot be referenced in join and where conditions.
        Use table names before column names to qualify the references to such
        columns. The <code class="literal">SET DATABASE SQL REFERENCES { TRUE | FALSE
        }</code> statement enables or disables this check.</p>
</li>
<li class="listitem">
<p>If the unqualified wild card is used, as in the statement SELECT
        * FROM ... no additional column references are allowed. A
        table-qualified wild card allows additional column references in the
        SELECT list</p>
</li>
<li class="listitem">
<p>Table definitions containing <code class="literal">GENERATED BY DEFAULT AS
        IDENTITY</code> but with no <code class="literal">PRIMARY KEY</code> do not
        automatically create a primary key. Database .script files made with
        1.8 are fine, as the <code class="literal">PRIMARY KEY</code> clause is always
        included. But the <code class="literal">CREATE TABLE</code> statements in your
        application program may assume an automatic primary key is created.
        The old shortcut, IDENTITY, is retained with the same meaning. So
        <code class="literal">CREATE TABLE T (ID IDENTITY, DAT VARCHAR(20))</code> is
        translated into <code class="literal">CREATE TABLE T(ID INTEGER GENERATED BY
        DEFAULT AS IDENTITY PRIMARY KEY, DAT VARCHAR(20))</code>. This last
        form is the correct way of defining both autoincrement and primary key
        in versions 1.8 and 2.0.</p>
</li>
<li class="listitem">
<p>CREATE ALIAS is now obsolete. Use the new function definition
        syntax. The <code class="classname">org.hsqldb.Library </code>class no longer
        exists. You should use the SQL form of the old library functions. For
        example, use <code class="literal">LOG(x)</code> rather than the direct form,
        <code class="literal">"org.hsqldb.Library.log"(x)</code>.</p>
</li>
<li class="listitem">
<p>The names of some commands for changing database and session
        properties have changed. See the list of statements in this
        chapter.</p>
</li>
<li class="listitem">
<p>Computed columns in SELECT statements which did not have an
        alias: These columns had no ResultMetaData label in version 1.8, but
        in version 2.0, the engine generates labels such as C1, C2.</p>
</li>
<li class="listitem">
<p>The issue with the JDBC ResultSetMetaData methods,
        <code class="literal">getColumnName(int column)</code> and
        <code class="literal">getColumnLabel(int column)</code> has been clarified by
        the JDBC 4 specification. <code class="literal">getColumName()</code> returns
        the underlying column name, while <code class="literal">getColumnLabel()</code>
        returns any specified or generated alias. HyperSQL 1.8 and 2.0 have a
        connection property, <code class="literal">get_column_name</code>, which
        defaults to true in version 2.0, but defaulted to false in some
        releases of version 1.8.x. You have to explicitly specify this
        property as false if you want (non-standard behaviour)
        <code class="literal">getColumnName()</code> to return the same value as
        <code class="literal">getColumnLabel()</code>.</p>
</li>
</ul>
</div>
</div>
<div class="section" title="HyperSQL Dependency Settings for Applications">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="dec_dependency_applications"></a>HyperSQL Dependency Settings for Applications</h2>
</div>
<div>
<h3 class="subtitle">Dependency settings using Gradle, Ivy, Maven, Groovy</h3>
</div>
</div>
</div>
<p>This section is about building applications that have build-time
    dependencies upon HyperSQL, and for executions that use a dependency
    library system. Examples of the second type are unit test runs, job runs
    triggered by a build system, or systems like Grape that pull libraries
    from the network at end-user run time.</p>
<div class="section" title="What version to Pull">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_hsqldb_version_pull"></a>What version to Pull</h3>
</div>
</div>
</div>
<p>The best option for most developers is to use our
      <span class="emphasis"><em>snapshot repository</em></span>, or at least to depend upon the
      latest public version of HyperSQL with a range pattern like
      <code class="literal">[2,)</code>. Here are exceptional cases where you should
      depend on a static version. </p>
<div class="itemizedlist">
<ul class="itemizedlist" type="disc">
<li class="listitem">Your application has code dependencies upon
            version-specific details of the HyperSQL distribution. In this
            case, the specific dependency specification should be checked in
            to your source code control system alongside the code that
            manifests the version-dependency. If your code is enhanced to use
            a newer version of HyperSQL, you should update the version
            specification so that whenever code + configs are checked out, the
            dependency will always match the code.</li>
<li class="listitem">Your organization only allows the use of vetted
            libraries. In this case, you vigorously maintain your
            configurations, updating your dependencies and regression testing
            as soon as new versions of HyperSQL are vetted. To get the best
            performance and reliability from HyperSQL, you should urge the
            appropriate parties to vet new versions as soon as they are
            publicly released.</li>
<li class="listitem">You need precisely reproducible builds.</li>
</ul>
</div>
<p>If none of these situations apply to you, then follow
      the suggestions in the appropriate sections below. If you need to
      specify a specific version, follow the instructions in the
      range-versioning section but change the version range specifications to
      literal versions like <code class="literal">2.2.9</code>.</p>
</div>
<div class="section" title="Using the HyperSQL Snapshot Repository">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_snapshot_repos"></a>Using the HyperSQL Snapshot Repository</h3>
</div>
<div>
<h4 class="subtitle">Use the Latest &amp; Greatest with Snapshots</h4>
</div>
</div>
</div>
<p>For application testing, you may want to use the latest HSQLDB
      snapshot jar instead of the latest release jar. Snapshot jars contain
      fixes for reported bugs and the new features as they are being tested
      for the next release version. You can use the snapshot jars where you
      would normally include a dependency to a release jar as a Maven
      artifact. The HyperSQL Snapshot repository resides at <a class="link" href="http://hsqldb.org/repos/" target="_top">http://hsqldb.org/repos/</a>
</p>
<div class="note" title="Limitation of Classifiers" style="margin-left: 0.5in; margin-right: 0.5in;">
<table border="0" summary="Note: Limitation of Classifiers">
<tr>
<td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left"><a name="dec_classifier_limit"></a>Limitation of Classifiers</th>
</tr>
<tr>
<td valign="top" align="left">
<p>
<span class="emphasis"><em>Classifiers</em></span> are incompatible with real
        repository <span class="emphasis"><em>snapshots</em></span>. Builders can only publish
        one jar variant per product, and at this time our snapshot jars are
        always built debug-enabled with Java 6.</p>
</td>
</tr>
</table>
</div>
<p>Where you insert the &lt;repository&gt; element depends on
      whether you want the definition to be personal, shared, or
      project-specific, so see the Maven documentation about that. But you can
      paste this element verbatim:</p>
<div class="example">
<a name="N16664"></a>
<p class="title">
<b>Example&nbsp;15.3.&nbsp;HyperSQL Snapshot Repository Definition</b>
</p>
<div class="example-contents">
<pre class="programlisting"> &lt;repository&gt;
   &lt;releases&gt;
     &lt;enabled&gt;false&lt;/enabled&gt;
   &lt;/releases&gt;
   &lt;snapshots&gt;
     &lt;enabled&gt;true&lt;/enabled&gt;
     &lt;updatePolicy&gt;always&lt;/updatePolicy&gt;
     &lt;checksumPolicy&gt;fail&lt;/checksumPolicy&gt;
   &lt;/snapshots&gt;
   &lt;id&gt;hsqldb_snapshots&lt;/id&gt;
   &lt;name&gt;HyperSQL Snapshots&lt;/name&gt;
   &lt;url&gt;http://hsqldb.org/repos&lt;/url&gt;
   &lt;layout&gt;default&lt;/layout&gt;
 &lt;/repository&gt;</pre>
</div>
</div>
<br class="example-break">
<div class="section" title="Snapshot Dependency Specification Examples">
<div class="titlepage">
<div>
<div>
<h4 class="title">
<a name="dec_hsqldb_snap_dep_examples"></a>Snapshot Dependency Specification Examples</h4>
</div>
</div>
</div>
<div class="example">
<a name="N1666D"></a>
<p class="title">
<b>Example&nbsp;15.4.&nbsp;Sample Snapshot Ivy Dependency</b>
</p>
<div class="example-contents">
<pre class="programlisting">
 &lt;dependency org="org.hsqldb" name="hsqldb" rev="SNAPSHOT" conf="buildOnly"/&gt;</pre>
</div>
</div>
<br class="example-break">
<div class="example">
<a name="N16672"></a>
<p class="title">
<b>Example&nbsp;15.5.&nbsp;Sample Snapshot Maven Dependency</b>
</p>
<div class="example-contents">
<pre class="programlisting">
 &lt;dependency&gt;
   &lt;groupId&gt;org.hsqldb&lt;/groupId&gt;
   &lt;artifactId&gt;hsqldb&lt;/artifactId&gt;
   &lt;version&gt;SNAPSHOT&lt;/version&gt;
   &lt;!-- Scope defaults to "compile":
   &lt;scope&gt;test&lt;/scope&gt;
   --&gt;
 &lt;/dependency&gt;</pre>
</div>
</div>
<br class="example-break">
<div class="example">
<a name="N16677"></a>
<p class="title">
<b>Example&nbsp;15.6.&nbsp;Sample Snapshot Gradle Dependency</b>
</p>
<div class="example-contents">
<pre class="programlisting">
  dependencies.compile (group: 'org.hsqldb', name: 'hsqldb', version:'SNAPSHOT')
  dependencies {
      runtime 'org.hsqldb:hsqldb:SNAPSHOT',
              'org.hsqldb:sqltool:SNAPSHOT'
  }</pre>
</div>
</div>
<br class="example-break">
<p>If you want to use an <code class="filename">ivy.xml</code> file with a
        Gradle build, you will need use the <a class="link" href="https://github.com/unsaved/gradle-ivyxml-plugin" target="_top"> Ivyxml
        Gradle Plugin</a>. It just takes a few links of code in your
        <code class="filename">build.gradle</code> file to hook in
        <code class="literal">ivyxml</code>. See the <a class="link" href="https://github.com/unsaved/gradle-ivyxml-plugin/raw/master/README.txt" target="_top">
        Ivyxml documentation</a> to see exactly how. </p>
<div class="example">
<a name="N1668F"></a>
<p class="title">
<b>Example&nbsp;15.7.&nbsp;Sample Snapshot ivy.xml loaded by Ivyxml plugin</b>
</p>
<div class="example-contents">
<pre class="programlisting">
  &lt;ivy-module version="2.0"&gt;
  ...
  &lt;dependency org="org.hsqldb" name="hsqldb" rev="SNAPSHOT"/&gt;</pre>
</div>
</div>
<p>
<br class="example-break">
</p>
<div class="example">
<a name="N16694"></a>
<p class="title">
<b>Example&nbsp;15.8.&nbsp;Sample Snapshot Groovy Dependency, using Grape</b>
</p>
<div class="example-contents">
<pre class="programlisting">
@Grab(group='org.hsqldb', module='hsqldb', version='SNAPSHOT')</pre>
</div>
</div>
<br class="example-break">
</div>
</div>
<div class="section" title="Range Versioning">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="dec_maven_range_version"></a>Range Versioning</h3>
</div>
<div>
<h4 class="subtitle">Keeping up-to-date with Range Dependencies</h4>
</div>
</div>
</div>
<div class="note" title="Limitation of Maven Version Range Specifiers" style="margin-left: 0.5in; margin-right: 0.5in;">
<table border="0" summary="Note: Limitation of Maven Version Range Specifiers">
<tr>
<td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left"><a name="dec_maven_range"></a>Limitation of Maven Version Range Specifiers</th>
</tr>
<tr>
<td valign="top" align="left">
<p>Note that Ivy (and the many systems that use Ivy underneath,
        like Grape and Gradle) supports the opening exclusive
        <code class="literal">]</code> in addition to <code class="literal">[</code>, whereas
        Maven supports only the opening inclusive <code class="literal">[</code>
        specifier. See the relevant <a class="link" href="http://ant.apache.org/ivy/history/latest-milestone/ivyfile/dependency.html" target="_top">
        Ivy</a> or <a class="link" href="http://docs.codehaus.org/display/MAVEN/Dependency+Mediation+and+Conflict+Resolution#DependencyMediationandConflictResolution-DependencyVersionRanges" target="_top">
        Maven</a> documentation for details. There are special cases where
        you should depend on a specific version instead.</p>
</td>
</tr>
</table>
</div>
<div class="section" title="Range Dependency Specification Examples">
<div class="titlepage">
<div>
<div>
<h4 class="title">
<a name="dec_hsqldb_dyn_dep_examples"></a>Range Dependency Specification Examples</h4>
</div>
</div>
</div>
<div class="important" title="Important" style="margin-left: 0.5in; margin-right: 0.5in;">
<table border="0" summary="Important">
<tr>
<td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th>
</tr>
<tr>
<td valign="top" align="left">
<p>For all examples below, when a range pattern is given, it
          means the latest version equal or greater than version
          <code class="literal">2</code>. If a classifier is shown, it is optional and
          you can skip it to get the default (no-classifier) jar.</p>
</td>
</tr>
</table>
</div>
<div class="example">
<a name="N166C0"></a>
<p class="title">
<b>Example&nbsp;15.9.&nbsp;Sample Range Ivy Dependency</b>
</p>
<div class="example-contents">
<pre class="programlisting">
 &lt;dependency org="org.hsqldb" name="hsqldb" rev="[2,)" conf="j6-&gt;default"/&gt;</pre>
<p>I give no example here of specifying a
          <span class="emphasis"><em>classifier</em></span> in <code class="filename">ivy.xml</code>
          because I have so far failed to get that to succeed. Classifiers in
          in <code class="filename">ivy.xml</code> are supported if using Gradle, as
          covered below.</p>
</div>
</div>
<br class="example-break">
<div class="example">
<a name="N166D0"></a>
<p class="title">
<b>Example&nbsp;15.10.&nbsp;Sample Range Maven Dependency</b>
</p>
<div class="example-contents">
<p>See <a class="link" href="deployment-chapt.html#dec_maven_range" title="Limitation of Maven Version Range Specifiers">note above</a>
          about Maven range specifications.</p>
<pre class="programlisting">
 &lt;dependency&gt;
   &lt;groupId&gt;org.hsqldb&lt;/groupId&gt;
   &lt;artifactId&gt;hsqldb&lt;/artifactId&gt;
   &lt;version&gt;[2,)&lt;/version&gt;
   &lt;!-- Scope defaults to "compile":
   &lt;scope&gt;test&lt;/scope&gt;
     Use a classifier to pull one of our alternative jars:
   &lt;classifier&gt;jdk5&lt;/classifier&gt;
   --&gt;
 &lt;/dependency&gt;</pre>
</div>
</div>
<br class="example-break">
<div class="example">
<a name="N166DB"></a>
<p class="title">
<b>Example&nbsp;15.11.&nbsp;Sample Range Gradle Dependency</b>
</p>
<div class="example-contents">
<pre class="programlisting">
 dependencies.compile (group: 'org.hsqldb', name: 'hsqldb', version:'[2,)', classifier: 'jdk5')
 dependencies {
     runtime 'org.hsqldb:hsqldb:[2,):jdk6debug@jar',
             'org.hsqldb:sqltool:[2,):jdk6debug@jar'
 }</pre>
</div>
</div>
<br class="example-break">
<p>If you want to use an <code class="filename">ivy.xml</code> file with a
        Gradle build, you will need use the <a class="link" href="https://github.com/unsaved/gradle-ivyxml-plugin" target="_top"> Ivyxml
        Gradle Plugin</a>. It just takes a few links of code in your
        <code class="filename">build.gradle</code> file to hook in
        <code class="literal">ivyxml</code>. See the <a class="link" href="https://github.com/unsaved/gradle-ivyxml-plugin/raw/master/README.txt" target="_top">
        Ivyxml documentation</a> to see exactly how. </p>
<div class="example">
<a name="N166F3"></a>
<p class="title">
<b>Example&nbsp;15.12.&nbsp;Sample Range ivy.xml loaded by Ivyxml plugin</b>
</p>
<div class="example-contents">
<pre class="programlisting">
 &lt;ivy-module version="2.0" xmlns:m="http://ant.apache.org/ivy/maven"&gt;
 ...
 &lt;dependency org="org.hsqldb" name="hsqldb" rev="[2,)" m:classifier="jdk5"/&gt;</pre>
</div>
</div>
<p>
<br class="example-break">
</p>
<div class="example">
<a name="N166F8"></a>
<p class="title">
<b>Example&nbsp;15.13.&nbsp;Sample Range Groovy Dependency, using Grape</b>
</p>
<div class="example-contents">
<pre class="programlisting">
@Grab(group='org.hsqldb', module='hsqldb', version='[2,)', classifier='jdk6debug')</pre>
</div>
</div>
<br class="example-break">
</div>
</div>
</div>
</div>
<HR xmlns:xi="http://www.w3.org/2001/XInclude">
<P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 5206 $</P>
<div class="navfooter">
<hr>
<table summary="Navigation footer" width="100%">
<tr>
<td align="left" width="40%"><a accesskey="p" href="unix-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a>&nbsp;</td><td align="center" width="20%">&nbsp;</td><td align="right" width="40%">&nbsp;<a accesskey="n" href="lists-app.html"><img src="../images/db/next.png" alt="Next"></a></td>
</tr>
<tr>
<td valign="top" align="left" width="40%">Chapter&nbsp;14.&nbsp;HyperSQL on UNIX&nbsp;</td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%">&nbsp;Appendix&nbsp;A.&nbsp;Lists of Keywords</td>
</tr>
</table>
</div>
</body>
</html>
